set SESSION sql_mode = 'ANSI'
The JDBC translator bridges between SQL semantic and data type difference between Teiid and a target RDBMS. Teiid has a range of specific translators that target the most popular open source and proprietary databases.
Type names:* jdbc-ansi - declares support for most SQL constructs supported by Teiid, except for row limit/offset and EXCEPT/INTERCECT. Translates source SQL into ANSI compliant syntax. This translator should be used when another more specific type is not available.
jdbc-simple- same as jdbc-ansi, except disables support for function, UNION, and aggregate pushdown.
access- for use with Microsoft Access 2003 or later.
db2- for use with DB2 8 or later and DB2 for i 5.4 or later.
DB2 specific execution properties:
DB2ForI- indicates that the the DB2 instance is DB2 for i. Defaults to false.
derby- for use with Derby 10.1 or later.
excel-odbc- for use with Excel 2003 or later via the JDBC-ODBC bridge.
greenplum- for use with the Greenplum database.
h2- for use with H2 version 1.1 or later.
hive- For use with Hive database based on Hadoop. Hive is a data warehousing infrastructure based on the Hadoop. Hadoop provides massive scale out and fault tolerance capabilities for data storage and processing (using the map-reduce programming paradigm) on commodity hardware. Hive has limited support for data types as it supports integer variants, boolean, float, double and string. It is does not have native support for time based types, xml or LOBs. These limitations are reflected in the translator capabilities. The view table can use these types, however the transformation would need to specify the necessary transformations. Note that in those situations, the evaluations will be done in Teiid engine. Another limitation Hive has is, it only supports EQUI join, so using any other joins types on its source tables will result in in-efficient queries. Currently there is no tooling support for metadata import from Hive in Designer. To write criteria based on partitioned columns, they can be modelled on source table, but do not include in selection columns.
hsql- for use with HSQLDB 1.7 or later.
ingres- for use with Ingres 2006 or later.
ingres93- for use with Ingres 9.3 or later.
intersystems-cache- for use with Intersystems Cache Object database (only relational aspect of it)
informix- for use with any Informix version.
metamatrix- for use with MetaMatrix 5.5.0 or later.
modeshape- for use with Modeshape 2.2.1 or later. The PATH, NAME, LOCALNODENAME, DEPTH, and SCORE functions should be accessed as pseudo-columns, e.g. "nt:base"."jcr:path". Teiid UFDs (prefixed by JCR_) are available for CONTIANS, ISCHILDNODE, ISDESCENDENT, ISSAMENODE, REFERENCE - see the JCRFunctions.xmi. If a selector name is needed in a JCR function, you should use the pseudo-column "jcr:path", e.g. JCR_ISCHILDNODE(foo.jcr_path, 'x/y') would become ISCHILDNODE(foo, 'x/y') in the ModeShape query. An additional pseudo-column "mode:properties" should be imported by setting the ModeShape JDBC connection property teiidsupport=true. The column "mode:properties" should be used by the JCR_REFERENCE and other functions that expect a .* selector name, e.g. JCR_REFERENCE(nt_base.jcr_properties) would become REFERENCE("nt:base".*) in the ModeShape query.
mysql/mysql5- for use with MySQL version 4.x and 5 or later respectively.
The MySQL Translators expect the database or session to be using ANSI mode. If the database is not using ANSI mode, an initialization query should be used on the pool to set ANSI mode:
set SESSION sql_mode = 'ANSI'
netezza - for use with any Netezza version.
oracle- for use with Oracle 9i or later. Sequences may be used with the Oracle translator. A sequence may be modelled as a table with a name in source of DUAL and columns with the name in source set to{{<sequence name>.[nextval|currentval].}} You can use a sequence as the default value for insert columns by setting the column to autoincrement and the name in source to <element name>:SEQUENCE=<sequence name>.<sequence value>. A rownum column can also added to any Oracle physical table to support the rownum pseudo-column. A rownum colum should have a name in source of rownum. These rownum columns do not have the same semantics as the Oracle rownum construct so care must be taken in their usage.
Oracle specific execution properties:
OracleSuppliedDriver- indicates that the Oracle supplied driver (typically prefixed by ojdbc) is being used. Defaults to true. Set to false when using DataDirect or other Oracle JDBC drivers.
postgresql- for use with 8.0 or later clients and 7.1 or later server.
sqlserver- for use with SQL Server 2000 or later. A SQL Server JDBC driver version 2.0 or later (or compatible e.g. JTDS 1.2 or later) should be used. SQL Server specific execution properties:
JtdsDriver- indicates that the open source JTDS driver is being used. Defaults to false.
sybase- for use with Sybase version 12.5 or later. Sybase specific execution properties:
JtdsDriver- indicates that the open source JTDS driver is being used. Defaults to false.
teiid- for use with Teiid 6.0 or later.
teradata- for use with Teradata V2R5.1 or later.
Execution Properties - shared by all JDBC Translators
|
Name |
Description |
Default |
|
DatabaseTimeZone |
The time zone of the database. Used when fetchings date, time, or timestamp values. |
The system default time zone |
|
DatabaseVersion |
The specific database version. Used to further tune pushdown support. |
The base supported version |
|
TrimStrings |
true to trim trailing whitespace from fixed length character strings. Note that Teiid only has a string, or varchar, type that treats trailing whitespace as meaningful. |
false |
|
UseBindVariables |
true to indicate that PreparedStatements should be used and that literal values in the source query should be replace with bind variables. If false only LOB values will trigger the use of PreparedStatements. |
true |
|
UseCommentsInSourceQuery |
This will embed a / comment / leading comment with session/request id in source SQL query for informational purposes |
false |
|
MaxPreparedInsertBatchSize |
The max size of a prepared insert batch. |
2048 |
Importer Properties - shared by all JDBC Translators
|
Name |
Description |
Default |
|
catalog |
See DatabaseMetaData.getTablesFull [1] |
null |
|
schemaPattern |
See DatabaseMetaData.getTables [1] |
null |
|
tableNamePattern |
See DatabaseMetaData.getTables [1] |
null |
|
procedureNamePattern |
See DatabaseMetaData.getProcedures [1] |
null |
|
tableTypes |
Comma separated list - without spaces - of imported table types. See DatabaseMetaData.getTables [1] |
null |
|
excludeTables |
A case-insensitive regular expression that when matched against a fully qualified Teiid table name will exclude it from import. Applied after table names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter. |
null |
|
excludeProcedures |
A case-insensitive regular expression that when matched against a fully qualified Teiid procedure name will exclude it from import. Applied after procedure names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter. |
null |
|
useFullSchemaName |
When false, directs the importer to drop the source catalog/schema from the Teiid object name, so that the Teiid fully qualified name will be in the form of <model name>.<table name> - Note: when false this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception. This option does not affect the name in source property. |
true |
|
importKeys |
true to import primary and foreign keys |
true |
|
importIndexes |
true to import index/unique key/cardinality information |
true |
|
importApproximateIndexes |
true to import approximate index information. See DatabaseMetaData.getIndexInfo [1] |
true |
|
importProcedures |
true to import procedures and procedure columns - Note that it is not always possible to import procedure result set columns due to database limitations. It is also not currently possible to import overloaded procedures. |
true |
|
widenUnsignedTypes |
true to convert unsigned types to the next widest type. For example SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte. |
true |
|
quoteNameInSource |
false will override the default and direct Teiid to create source queries using unquoted identifiers. |
true |
|
useProcedureSpecificName |
true will allow the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure specific name as the Teiid name. This option will only work with JDBC 4.0 compatible drivers that report specific names. |
false |
|
useCatalogName |
true will use any non-null/non-empty catalog name as part of the name in source, e.g. "catalog"."schema"."table"."column", and in the Teiid runtime name if useFullSchemaName is also true. false will not use the catalog name in either the name in source or the Teiid runtime name. Should be set to false for sources that do not fully support a catalog concept, but return a non-null catalog name in their metadata - such as HSQL. |
true |
|
useQualifiedName |
true will use name qualification for both the Teiid name and name in source as dictated by the useCatalogName and useFullSchemaName properties. Set to false to disable all qualification for both the Teiid name and the name in source, which effectively ignores the useCatalogName and useFullSchemaName properties. Note: when false this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception. |
true |
[1] JavaDoc for DatabaseMetaData
The default import settings will crawl all available metadata. This import process is time consuming and full metadata import is not needed in most situations. Most commonly you'll want to limit the import by at least schemaPattern and tableTypes.
Example importer settings to only import tables and views from my-schema.
... <property name="importer.tableTypes" value="TABLE,VIEW"/> <property name="importer.schemaPattern" value="my-schema"/> ...
Usage of a JDBC source is straight-forward. Using Teiid SQL, the source may be queried as if the tables and procedures were local to the Teiid system.
Both physical tables and procedures may optionally have native queries associated with them. No validation of the native query is performed, it is simply used in a straight-forward manner to generate the source SQL. For a physical table setting the teiid_rel:native-query extension metadata will execute the native query as an inline view in the source query. This feature should only be used against sources that support inline views. The native query is used as is and is not treated as a parameterized string. For example on a physical table y with nameInSource="x" and teiid_rel:native-query="select c from g", the Teiid source query"SELECT c FROM y" would generate the SQL query "SELECT c FROM (select c from g) as x". Note that the column names in the native query must match the nameInSource of the physical table columns for the resulting SQL to be valid.
For physical procedures you may also set the teiid_rel:native-query extension metadata to a desired query string with the added ability to positionally reference IN parameters - see Parameterizable Native Queries. The teiid_rel:non-prepared extension metadata property may be set to false to turn off parameter binding. Note this option should be used with caution as inbound may allow for SQL injection attacks if not properly validated. The native query does not need to call a stored procedure. Any SQL that returns a result set positionally matching the result set expected by the physical stored procedure metadata will work. For example on a stored procedure x with teiid_rel:native-query="select c from g where c1 = $1 and c2 = '$$1'", the Teiid source query "CALL x(?)" would generate the SQL query "select c from g where c1 = ? and c2 = '$1'". Note that ? in this example will be replaced with the actual value bound to parameter 1.
This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the translator property called "SupportsNativeQueries" to true. Look for Override Execution Properties above.
JDBC translator also provides a procedure with name native that gives ability to execute any ad-hoc native SQL command that is specific to an underlying source directly against the source without any Teiid parsing or resolving. The metadata of this procedure's execution results are not known to the Teiid, and they are returned as object array. User can use ARRAYTABLE construct to build a tabular output for consumption by client applications. Example execution can be like below
SELECT x.* FROM (call pm1.native('select * from g1')) w,
ARRAYTABLE(w.tuple COLUMNS "e1" integer , "e2" string) AS x
SELECT x.* FROM (call pm1.native('insert into g1 (e1,e2) values (?, ?)', 112, 'foo')) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call pm1.native('update g1 set e2=? where e1 = ?','blah', 112)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call pm1.native('delete from g1 where e1 = ?', 112)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
By default the name of the procedure that executes the queries directly is called native, however user can set Override Execution Properties property on NativeQueryProcedureName in vdb.xml file to change it to any other procedure name.
The resource adapter for this translator provided through data source in JBoss AS, Refer to Admin Guide for "JDBC Data Sources" configuration section.